﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DSheet.Core.Data;
using System.Data.SqlClient;
using System.Data;

namespace DSheet.Core
{
    public class DocInstanceService
    {
        public bool CreateInstance(DBContext context, DocInstance docInstance)
        {
            string sql = "insert into docinstance (id,yearid,bdid,orgid,templateid,name,filepath) values (@id,@yearid,@bdid,@orgid,@templateid,@name,@filepath)";
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", docInstance.Id));
            command.Parameters.Add(new SqlParameter("@yearid", docInstance.YearId));
            command.Parameters.Add(new SqlParameter("@bdid", docInstance.BDId));
            command.Parameters.Add(new SqlParameter("@orgid", docInstance.OrgId));
            command.Parameters.Add(new SqlParameter("@templateid", docInstance.TempalteId));
            command.Parameters.Add(new SqlParameter("@name", docInstance.Name));
            command.Parameters.Add(new SqlParameter("@filepath", docInstance.FilePath));

            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public List<DocInstance> GetAllInstance(DBContext context, Guid yearid, Guid bdId, Guid orgId)
        {
            List<DocInstance> instances = new List<DocInstance>();

            string sql = "select * from docinstance where yearid=@yearid and bdid=@bdid and orgid=@orgid";
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@yearid", yearid));
            command.Parameters.Add(new SqlParameter("@bdid", bdId));
            command.Parameters.Add(new SqlParameter("@orgid", orgId));

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet instanceSet = new DataSet();
            adapter.Fill(instanceSet);
            foreach (DataRow dtRow in instanceSet.Tables[0].Rows)
            {
                DocInstance instance = new DocInstance();

                instance.Id = new Guid(Convert.ToString(dtRow["id"]));
                instance.YearId = new Guid(Convert.ToString(dtRow["yearid"]));
                instance.BDId = new Guid(Convert.ToString(dtRow["bdid"]));
                instance.OrgId = new Guid(Convert.ToString(dtRow["orgid"]));
                instance.TempalteId = new Guid(Convert.ToString(dtRow["templateid"]));
                instance.FilePath = Convert.ToString(dtRow["filepath"]);
                instance.Name = Convert.ToString(dtRow["name"]);

                instances.Add(instance);
            }
            return instances;
        }

        public DocInstance GetInstance(DBContext context, Guid yearid, Guid bdid, Guid orgid, Guid templateid)
        {
            DocInstance instance = null;
            string sql = "select * from docinstance where yearid=@yearid and bdid=@bdid and orgid=@orgid and templateid=@templateid";
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@yearid", yearid));
            command.Parameters.Add(new SqlParameter("@bdid", bdid));
            command.Parameters.Add(new SqlParameter("@orgid", orgid));
            command.Parameters.Add(new SqlParameter("@templateid", templateid));

            SqlDataReader reader = command.ExecuteReader();
            if(reader.Read())
            {
                instance = new DocInstance();
                instance.Id = new Guid(Convert.ToString(reader["id"]));
                instance.YearId = new Guid(Convert.ToString(reader["yearid"]));
                instance.BDId = new Guid(Convert.ToString(reader["bdid"]));
                instance.OrgId = new Guid(Convert.ToString(reader["orgid"]));
                instance.TempalteId = new Guid(Convert.ToString(reader["templateid"]));
                instance.FilePath = Convert.ToString(reader["filepath"]);
                instance.Name = Convert.ToString(reader["name"]);
            }
            reader.Close();

            return instance;
        }
        public DocInstance GetInstance(DBContext context, Guid id)
        {
            DocInstance instance = null;
            string sql = "select * from docinstance where id=@id";
            SqlCommand command = context.DBConnection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", id));

            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                instance = new DocInstance();
                instance.Id = new Guid(Convert.ToString(reader["id"]));
                instance.YearId = new Guid(Convert.ToString(reader["yearid"]));
                instance.BDId = new Guid(Convert.ToString(reader["bdid"]));
                instance.OrgId = new Guid(Convert.ToString(reader["orgid"]));
                instance.TempalteId = new Guid(Convert.ToString(reader["templateid"]));
                instance.FilePath = Convert.ToString(reader["filepath"]);
                instance.Name = Convert.ToString(reader["name"]);
            }
            reader.Close();

            return instance;
        }
    }
}
